<html>
<head>
<title>firstworks   SQL Relay FAQ</title>
<link href="css/styles.css" rel="stylesheet">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
</head>
<body>

<span class="heading1">SQL Relay FAQ</span><br><br>

<span class="heading2">一般性问题</span><br><br>
<ol>
<li><a href="#whatis">什么是SQL Relay?</a></li>
<li><a href="#doforme">SQL Relay能为我做什么?</a></li>
<li><a href="#platforms">SQL Relay能够运行在什么平台上?</a></li>
<li><a href="#howwork">SQL Relay是如何工作的?</a></li>
<li><a href="#efficiency">SQL Relay如何提高我的网站的效率?</a></li>
<li><a href="#oracle">为什么当我需要把我的基于web的数据库驱动应用程序迁移到Oracle上时，
使用SQL Relay格外的好？ </a></li>
<li><a href="#freebsd">
为什么SQL Relay对于Open/Net/FreeBSD及PowerPC linux格外的好？</a></li>
<li><a href="#pooling">我能使用SQL Relay作为数据库连接池吗？</a></li>
<li><a href="#overloaded">
SQL Relay能够是我的数据库摆脱负载过高的情况吗？</a></li>
<li><a href="#replicated">
如何将SQL Relay应用于集群或者多台数据库的情况？</a></li>
<li><a href="#firewall">
我能使用SQL Relay进行<font color="=Red">防火墙自组织网络查询
(firewall ad-hoc queries)</font>吗？</a></li>
<li><a href="#databaseusers">
SQL Relay代理多个数据库用户这种机制能够代替为每一个数据库会话使用同样的用户这样的机制吗？</a></li>
<li><a href="#routing">我能使用SQL Relay把所有的更新操作发送到主数据库，把所有的查询操作发送到
一组从数据库吗？</a></li>
<li><a href="#mysqlrouting">我能利用SQL Relay让我现有的MYSQL应用程序把所有的更新操作发送至一台主数据库，
把查询操作发送到一系列从服务器吗？</a></li>
<li><a href="#filtering">SQL Relay能配置成过滤特定的查询吗？例如没有where条件的select语句</a></li>
</ol>

<span class="heading2">API 比较问题</span><br><br>
<ol>
<li><a href="#cgi">为什么要以CGI的形式使用SQL Relay而不是用一个原本的数据库API</a></li>
<li><a href="#odbc">SQL Relay与ODBC，JDBC，Perl::DBI, PHP::ADODB,Ruby::DBI or PythonDB相比如何？</a></li>
<li><a href="#dbiproxy">SQL Relay与DBI::proxy相比如何？</a></li>
<li><a href="#apachedbi">SQL Relay与Apache::DBI及PHP内建的数据库常连接函数相比如何？</a></li>
<li><a href="#zope">为什么我应该在Zope里使用SQL Relay？</a></li>
</ol>

<span class="heading2">数据库相关问题</span><br><br>
<ol>
<li><a href="#canxxxdb">我能使用SQL Relay连接xxx数据库吗？</a></li>
<li><a href="#whyxxxdb">为什么我应该用SQL Relay连接xxx数据库？</a></li>
<li><a href="#features">SQL Relay是否支持XXX数据库的特有特性？</a></li>
<li><a href="#sqlserver">为什么我的SQL Relay连接不上 Sybase/Microsoft SQL Server?</a></li>
<li><a href="#freetds">我知道Sybase和MS SQL Server支持受影响的行数(affected row counts), 
但是为什么FreeTDS连接对于该数量的返回是-1？</a></li>
<li><a href="#money">为啥货币(money)类型让我的FreeTDS连接崩溃了？</a>
<li><a href="#sybasefreetdsdiff">Sybase和FreeTDS连接之间有什么不同？</a></li>
<li><a href="#postgresql">Postgresql自身的API使用数字(number)作为类型，但是SQL Relay
损毁<font color="Red">(mangles 还没弄明白是啥意思)</font>他们，我如何获取数字？</a>
<li><a href="#nlslang">我正在使用一个非ascii字符集的oracle，
为什么当我select的时候获得了一堆垃圾数据？</a></li>
<li><a href="#sqliterowid">我如何从SQLite获取最后插入id(last insert rowid)</a></li>
<li><a href="#oracleosauth">我如何使用Oracle的OS-Authentication
(用户外部认证users identeified externally)？</a></li>
<li><a href="#mysqltrans">为什么commit操作对mysql不起作用？</a></li>
<li><a href="#db2login">为什么第一次DB2 的查询会使得SQL Relay从新连接数据库</a></li>
</ol>

<span class="heading2">编程问题</span><br><br>
<ol>
<li><a href="#php">我的php程序用光了所有的SQL Relay连接，我只好不停的重启SQL Relay，怎么办？</a></li>
<li><a href="#buffer">为什么SQL Relay要缓存整个结果集？</a></li>
<li><a href="#nobuffer">如何让SQL Relay不要缓存整个结果集</a></li>
<li><a href="#bindvars">我能使用绑定变量吗？</a></li>
<li><a href="#vectorbinds">为什么我不能做向量(vector)绑定？</a></li>
<li><a href="#storedprocs">我如何运行存储过程</a></li>
<li><a href="#returning">我怎样才能使用RETURNING条件获取DML的数据</a></li>
<li><a href="#temptables">SQL Relay可以使用临时表机制吗？</a></li>
<li><a href="#threadsafe">SQL Relay是线程安全的吗？</a></li>
<li><a href="#clientcache">客户端结果集缓存机制是如何工作的？</a></li>
<li><a href="#servercache">服务器端的缓存机制呢？</a></li>
<li><a href="#defineoutputbind">定义输出绑定(defineOutputBind)如何运作？</a></li>
<li><a href="#mysqlinsertid">MySQL提供了mysql_insert_id()这个函数，在SQL Relay里用什么？</a></li>
</ol>

<span class="heading2">系统管理问题</span><br><br>
<ol>
<li><a href="#tweaks">如何调整系统参数以获得SQL Relay更好的性能？</a></li>
<li><a href="#firewallhang">如果我让SQL Relay空闲了一段很长的时间，然后我尝试使用它，
我发现数据库会发出一个低级的TCP错误，这意味着什么？</a></li>
<li><a href="#ttl">如果我设置connections=0和maxconnections为一个大一点儿的数，
并且让SQL Relay空闲很长一段时间，那么做后一个连接会死掉(die off)?</a></li>
<li><a href="#nobody">如果我设置成maxconnections&gt;connections，则启动不了新的连接，
并且在屏幕上得到"Couldn't parse config file ..."的错误，这是怎么回事？</a></li>
<li><a href="#gui">配置GUI会到哪儿去
<font color="Red">(where'd the configuration GUI go)</font>？</a></li>
<li><a href="#socketonly">我怎么样做才能让SQL Relay只在unix socket端口上监听，
而不会在inet端口上监听？</a></li>
<li><a href="#timequeries">我怎么样做才能让SQL Relay记录查询日志？</a></li>
<li><a href="#dynamicscaling">如果我把SQL Relay配置成动态范围，
它会生成尽可能多的链接并且从不会关闭，这是怎么回事？</a></li>
</ol>


<span class="heading2">一般性问题</span><br><br>

<a name="whatis"></a>
<span class="heading3">什么是SQL Relay?</span><br><br>

<p>
SQL Relay是一个运行在Unix和Linux系统上的数据库连接池，代理和负载均衡软件，支持ODBC，
Oracle，Mysql，PostgreSQL，MS SQL Server，IBM DB2，FIrebird，SQLite和
MS ACCESS（最小化），为C，C++，Perl，Perl-DBD，Python，Python-DB，Zope，PHP，Ruby，
Ruby-DBD，Java和TCL提供了API，对于Mysql和PostgreSQL客户端提供了即插即拔替换库，还有
一个命令行客户端和很多的文档。 它的API支持高级数据库操作，比如变量邦定，多行fetch，
客户端结果集缓存及事务挂起。 对于数据库驱动的基于web的应用程序，访问不支持平台的数据库，
不同数据库的迁移，分布式访问，访问控制等是一个理想的软件。</p>

<a name="doforme"></a>
<span class="heading3">SQL Relay能为我做什么?</span>

<p>
SQL Relay能够提高数据库驱动的web应用程序的效率，提供不支持平台数据库的访问，
不同数据库的平滑迁移，分布式访问及访问控制。</p>

<p>
SQL Relay并不完全仅仅是一个”即插即用“的解决方案，他需要进行一些配置，如果能够
在写一些程序将会更有用</p>

<a name="platforms"></a>
<span class="heading3">SQL Relay能够运行在什么平台上?</span>

<p>
Unix系列的平台。目前已知的是能续行在Linxu（x86和PPC），SCO 开放服务器，Solaris，FreeBSD，
OpenBSD和NetBSD。客户端API可以利用Cygwin或者UWIN运行于Win32平台之上。我只访问过Linux，SCO，
Solaris，FreeBSD，OpenBSD，NetBSD和Win32系统，因此时不时的一个新的版本出来之后可能会打破
对于其他平台的兼容性。如果有一个平台你希望看到能够支持，那么请你给我这个系统的访问授权，同时请
发邮件给我：<a href="mailto:david.muse@firstworks.com">david.muse@firstworks.com</a>.
</p>

<a name="howwork"></a>
<span class="heading3">SQL Relay是如何工作的?</span>

<p>
SQL Relay的连接后台程序登录到数据库，并且维护一些绘画。这些连接后台程序使用一个监听后台程序
来发布连接后台程序，他在inet或者unix端口上监听客户端的链接，当一个客户端连接被监听到时，
如果某一个连接可用，那么监听程序就把客户端的链接交给这个链接，如果此时没有空闲的连接，客户端
进入一个等待队列直到有一个连接可用。 一旦一个客户端连接交给了连接进程，客户端就通过这些后台
程序维护的会话来与数据库进行交互。</p>

<a name="efficiency"></a>
<span class="heading3">SQL Relay如何提高我的网站的效率?</span>

<p>
这有一些SQL Relay能够提高你的网站效率的一些例子</p>

<p>
让我们假设你正在使用CGI程序访问一个事务型的数据库，比如PostgreSQL，MS SQL Server或者
Oracle。 CGI程序每次运行时必须登录和登出数据库，如果你使用SQL Relay来维护一些持续的数据库连接，只需要使用SQL Relay登录和登出数据库，那么你就可以减少花费在建立数据库连接上的时间，
每秒钟可以处理更多CGI请求。这一方面是因为花费在与SQL Relay连接上的时间比花费在与数据库连接
上的时间要短，另一方面SQL Relay客户端库比数据库客户端库也更小，因此便形成一个更加轻量级的CGI。
</p>

<p>
假设你正在使用Apache，PHP和Oracle，你决定使用所有种类的分析得出你需要运行30个Apache进程
来提供合适的相应。如果你的站点不是数据库驱动的，平均同时不超过5个PHP程序访问数据库。
现在，你正在使用常链接来战胜耗时的登录Oracle操作，但是你不得不维持30个链接（每个web进程一个
链接），他们会在客户端及数据库端消耗掉很多内存，事实上你只需要5个Oracle连接，但是只能继续
运行30个apache进程并且在两端的机器上浪费内存。</p>

<p>
很多网站同时运行着PHP和Perl模块，Perl模块能够使用Apache::DBI，PHP有一个数据库常连接系统，但是
一个php程序却不能使用Apache::DBI连接模块，而Perl模块不能使用PHP的常连接。于是为了保证每个
平台最大的数据库连接，更多的web服务器进程需要运行，可能是两倍之多。如果PHP和Perl程序都使用
SQL Relay，他们可以共享数据库连接，并且减少Web服务器进程和数据库连接的数量。</p>

<p> 
SQL Relay很容易就能实现在多台机器间平衡负载。当时用MySQL或者PostgreSQL在一个只读的
web环境上，一个普通的规模化方案是运行多台web服务器和多个数据库服务器，定期去同步所有的
数据库服务器。通常情况这样的方案运行的不错，但是有时某个数据库服务器或者web服务器可能会
负载很高，而其他的服务器则是空闲的。在其他的情况下，需要一个不规则的数量的服务器。例如，
你的应用程序需要三台web服务器和2台数据库服务器，人们通常会为web和数据库各买三台服务器，
这样浪费了钱，而且在大多数情况下服务器还必须是同等强大的机器，你不能添加一台便宜的机器直到
你变穷。SQL Relay能够链接多台，重复或者簇数据库服务器，提供基于web的应用程序访问其中空闲的
机器。它还能够配置成保持更多的连接到更强大的服务器，少量的连接到性能低下的机器，以保持同一个
数据库连接池的平衡。这些特点可以按您所需，让你使用准确的服务器数量，正确的节省金钱，
</p>


<a name="oracle"></a>
<span class="heading3">为什么当我需要把我的基于web的数据库驱动应用程序迁移到Oracle上时，
使用SQL Relay格外的好？</span>

<p>
连接到Oracle数据库尤其是一个耗时的操作，而且OCI库与其他数据库相比是一个重量级的库，把这些
缺点从你的应用程序中移除对于使用Oracle数据库来说更是一件有利的事情。</p>

<a name="freebsd"></a>
<span class="heading3">
为什么SQL Relay对于Open/Net/FreeBSD及PowerPC linux格外的好？</span>

<p>
Open/Net/FreeBSD和非-x86的Linux是很好的平台但是缺少杰出的商业数据库厂商的API支持。
SQL Relay提供了对这些平台的连接解决方案。
</p>

<a name="pooling"></a>
<span class="heading3">我能使用SQL Relay作为数据库连接池吗？</span>

<p>可以</p>

<p>
SQL Relay维护着一些数据库常连接，客户端能够通过inet或者unix socke的轻量级TCP连接来共享
这些连接。这意味着SQL Relay能够用于多台机器交叉的数据库连接池。</p>

<a name="overloaded"></a>
<span class="heading3">SQL Relay能够是我的数据库摆脱负载过高的情况吗？</span>

<p>可以。</p>

<p>
对于流量大，数据库驱动的web站点，一个常见的问题就是为了解决众多的访问请求，必须运行大量
的web服务进程或者线程。使用传统的连接池机制，每个进程至少要维护一个数据库常连接。有时，
在负载比较高的情况下，数据库服务器无法处理同时这么多的客户端连接。</p>

<p>
簇是一种解决方案，但是簇过于昂贵而且并不使用所有的数据库。</p>

<p>
通过在web服务器和数据库之间部署SQL Relay，你可以维护一个较小数量的数据库常连接，通过这些
连接来处理所有的数据库请求。当数据库会话的需求数量超过的常连接的数量是，会话的请求会放入一个
队列。这样会造成客户端延迟相应，但是保持数据库平滑的运行。在大多数情况下，这种延迟可以
被忽略或者可以接受。</p>

<a name="replicated"></a>
<span class="heading3">如何将SQL Relay应用于集群或者多台数据库的情况？</span>

<p>
如果你有用集群或簇数据库，SQL Relay可以配置成对于部分或者全部的数据库服务器保持常连接，
并且将会话分散在这些数据库之中。SQL Relay甚至可以配置成对于性能强劲的服务器维护更多的
数据库连接，对于性能较低的机器维持较少的数据库连接，允许数据库连接池以一种机器异质混合
的形式使用。</p>

<p>
要注意的是SQL Relay不能用于集群数据库或者说使集群数据库同步，如果你使用SQL Relay访问
集群数据库，那么必须要假设有某种外部方法能够对数据库进行同步。</p>

<a name="firewall"></a>
<span class="heading3">
我能使用SQL Relay进行<font color="=Red">防火墙自组织网络查询
(firewall ad-hoc queries)</font>吗？</span>

<p>不可以.</p>

<p>这个特性还在TODO列表上</p>

<a name="databaseusers"></a>
<span class="heading3">SQL Relay代理多个数据库用户这种机制能够代替为每一个数据库会话使用同样的用户这样的机制吗？</span>

<p>
可以，在sqlrelay.conf文件中对"database"设置实例标签的授权属性，具体请见
<a href="configuring.html">配置SQL Relay</a></p>

<p>
当使用Oracle 8i或者更高版本和Mysql 3.23.3或者更高版本时，SQL Relay对于此特性非常有效率。
对于Oracle，数据库必须进行相应的配置。请看<a href="oraclentier.html">this document</a>
中的具体步骤指示</p>

<p>
Oracle 8i及更高版本和Mysql 3.23.3及更高版本允许一个连接至数据库的进程能够不用从数据库
断开就可以切换用户。当使用其他数据库时，SQL Relay如果需要切换用户，会登出然后在登入至数据库。</p>

<a name="routing"></a>
<span class="heading3">
我能使用SQL Relay把所有的更新操作（insert/update/delete）发给主数据库，而把所有的
查询（select）操作发送给其他的一系列从数据库吗？</span>

<p>
当然，对于0.38版本，请看<a href="router.html">使用SQL Realy进行路由和过滤</a>
</p>

<a name="mysqlrouting"></a>
<span class="heading3">
我能使用SQL Relay使我已有的MySQL应用程序把所有的更新操作发给主数据库，而把所有的
查询操作发给其他的一系列从数据库吗？</span>

<p>
也许可以，对于0.38版本，你需要对SQL Relay进行查询路由配置，并且使用
<font color=red>插件库（暂且翻译成插件库，实际是Drop-in Replacement）</font>
来让你的MySQL对应用程序的查询进行跳转。</p>

<p>请看 <a href="router.html">使用SQL Realy进行路由和过滤</a>以获得关于设置
SQL Relay成一个查询路由的详细信息
</p>

<p>请看 <a href="dropin/mysql.html">使用SQL Relay插件库</p>

<a name="filtering"></a>
<span class="heading3">
SQL Relay能设置成过滤特定的查询吗？例如没有where条件的select语句？
</span>

<p>
当然！对于0.38版本，请看<a href="router.html">使用SQL Realy进行路由和过滤
</a>.</p>


<span class="heading3">API 比较问题</span><br><br>

<a name="cgi"></a>
<span class="heading3">
为什么要以CGI的形式使用SQL Relay而不是作为一个本地的数据库API？
</span>

<p>
CGI程序运行时，必须要登入登出数据库，这需要花费较长的时间。本地数据库API库通常
情况下比较大。由于SQL Repay使用数据库常连接的方式，因此可以很快建立连接并且具有
一个轻量级的客户端API，以CGI的方式运行SQL Relay通常能够使用更少的内存，执行更
快的应用程序。
</p>

<a name="odbc"></a>
<span class="heading3">SQL Relay与ODBC，JDBC，Perl::DBI, PHP::ADODB,Ruby::DBI or PythonDB相比如何？</span>

<p>
这其实是一个个风马牛不相及的比较，这些API的所实现的是一个抽象层，而不是为了提高
应用程序的性能，他们在一定程度上比SQL Relay客户端API具有更完整的特性。SQL Relay
的API支持Perl::DBI，Ruby::DBI和PythonDB，客户端连接支持ODBC，而对ODBC API的支持
已经在TODO列表中了。
</p>

<a name="dbiproxy"></a>
<span class="heading3">SQL Relay与DBI::proxy相比如何？</span>

<p>
DBI::Proxy是一个Perl特有的，或者至少对其他语言来说比较有挑战性的API。
由于DBI::Proxy主要目标是提供跨平台的数据库的访问，因此SQL Relay
不仅仅在提高应用程序性能方面可能会超过DBI::Proxy。SQL Relay也提供跨平台数据库访问，
甚至是不支持unix的ODBC连接和ODBC连接桥（no unix support using the ODBC connection and an ODBC to ODBC 
bridge）。
</p>

<a name="apachedbi"></a>
<span class="heading3">
SQL Relay与Apache::DBI及PHP内建的数据库常连接函数相比如何？</span>

<p>
SQL Relay更加轻量级，并且可能比Apache::DBI更快，与PHP常连接的速度不向上下。
SQL Relay能够提供对多台机器的连接池，并且相比Apache::DBI或者php有更多后端的特性。
然而，DBD和PHP API的特性相比SQL Relay的API更加完整，但一般来说SQL Relay比较简单易用。
</p>

<p>
当使用Apache::DBI或者PHP常连接时，每一个web服务器进程都会打开一个连接。
通常web需要运行很多个进程来提供相应的响应，数据库连接数增长会导致资源
愈来愈少，但是数据库连接在大部分时间是空闲的。
</p>

<p>
如果一个站点同时运行着perl和PHP脚本，那么问题通常会增加一倍。
</p>

<p>
SQL Replay通过使用保持少量的常连接来提高资源利用率，利用这些常连接来处理
所有的数据库请求。当数据库会话需求超过了常连接的数量时，后来的请求将会排队，
这样会导致在客户端产生一定的时延，但是能够保证数据库平稳的运行，大多数情况
下，延迟是可以忽略不计并且可以接受的。
</p>

<a name="zope"></a>
<span class="heading3">
为什么我要结合着Zope来使用SQL Relay?
</span>

<p>
对于Apache::DBI和PHP常连接的效率之争并不适用于Zope。Zope在其缓存中保持一个
可以配置数量的数据库常连接，通过线程来共享这些常连接。数据库常连接和线程的
数量是分别独立的。始终存在着一种可能性，那就是1个或者所有的数据库连接都从
缓存中拉出来以至于不得不在这之后进行备份，但是在实际中，这种情况很少发生。
</p>

<p>
如果你有这样一些Zope机器，数据库常连接的数量在过分使用数据库服务器的资源，SQL
Relay能够成为Zope和服务器之间的纽带，来减少数据库常连接的数量。
</p>

<p>
SQL Relay能够提供一些服务器的即插即用和负载均衡功能给Zope。
</p>

<p>SQL Relay能够在没有Zope适配器的情况下提供链接数据库的方法。
</p>

<p>


当使用ZOracleDa时，如果数据库<font color='red'>切换</font>，Zope通常需要重启。
而使用SQL Relay, 数据库切换也可以不用重启。这个行为尽管对ZOracleDA是一个特例，
单可能只是一个bug。SQL　ReLay也支持Oralce LOB和长数据类型，由于ZOracleDA使用
OCI7而不是OCI8调用，因此不支持这些数据类型
</p>

<span class="heading3">Database-Specific Questions
数据库相关问题
</span><br><br>

<a name="canxxxdb"></a>
<span class="heading3">我能使用SQL Relay连接XXX数据库吗?</span>

<p>
SQL Relay能连接Oracle，MySQL， PostgreSQL，Sybas，DB2,Firebird和SQLite数据库，
他是将这些数据库的API编译过后使用一个守护进程进行连接的。另外，SQL Relay能够使用
FreeTDS编译库来连接Microsoft SQL Server或者Sybase数据库，使用MDBTools编译库来
连接MS Access Database。通过使用ODBC连接（由iODBC或unixODBC编译），SQL Relay
能够在unix上连接任意一个具有ODBC驱动的数据库，或者使用ODBC和ODBC桥在任意平台上连接
任意具有ODBC驱动的数据库。
</p>

<a name="whyxxxdb"></a>
<span class="heading3">为什么我要用SQL Relay来连接xxx数据库？</span>

<br><br>
<table border="1">
<tr>
<td><b>Database</b></td>
<td><b>Limit the number of open connections.</b></td>
<td><b>Distribute over replicated or clustered databases.</b></td>
<td><b>Overcome the connection delay.</b></td>
<td><b>Provide remote access.</b></td>
</tr>
<tr>
<td><b>Oracle</b></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
</tr>
<tr>
<td><b>MySQL</b></td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td><b>PostgreSQL</b></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
</tr>
<tr>
<td><b>Sybase</b></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
</tr>
<tr>
<td><b>DB2</b></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
</tr>
<tr>
<td><b>Firebird</b></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
</tr>
<tr>
<td><b>SQLite</b></td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
<td>Yes</td>
</tr>
<tr>
<td><b>FreeTDS</b></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
</tr>
<tr>
<td><b>ODBC</b></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
</tr>
<tr>
<td><b>MDB Tools</b></td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
<td>Yes</td>
</tr>
</table>
<br><br>

<a name="features"></a>
<span class="heading3">Does SQL Relay support XXX's specific features?</span>

<br><br>
<table border="1">
<tr>
<td><b>Database</b></td>
<td><b>Queries</b></td>
<td><b>Bind Variables</b></td>
<td><b>Procedural Language</b></td>
<td><b>Auto-Commit</b></td>
</tr>
<tr>
<td><b>Oracle</b></td>
<td>Yes</td>
<td>Scalar Input/Output</td>
<td>Yes</td>
<td>Yes</td>
</tr>
<tr>
<td><b>MySQL</b></td>
<td>Yes</td>
<td>Scalar Input</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td><b>PostgreSQL</b></td>
<td>Yes</td>
<td>Scalar Input</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td><b>Sybase</b></td>
<td>Yes</td>
<td>Scalar Input</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td><b>DB2</b></td>
<td>Yes</td>
<td>Scalar Input/Output</td>
<td>Unknown</td>
<td>Yes</td>
</tr>
<tr>
<td><b>Firebird</b></td>
<td>Yes</td>
<td>Scalar Input</td>
<td>Unknown</td>
<td>Yes</td>
</tr>
<tr>
<td><b>SQLite</b></td>
<td>Yes</td>
<td>Scalar Input</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td><b>FreeTDS</b></td>
<td>Yes</td>
<td>Scalar Input</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td><b>ODBC</b></td>
<td>Yes</td>
<td>Scalar Input/Output if DB supports it.</td>
<td>Yes if DB supports it.</td>
<td>Yes if DB supports it.</td>
</tr>
<tr>
<td><b>MDB Tools</b></td>
<td>Yes</td>
<td>Scalar Input</td>
<td>No.</td>
<td>No.</td>
</tr>
</table>
<br><br>

<a name="sqlserver"></a>
<span class="heading3">Why can't I get SQL Relay to connect to Sybase/Microsoft
SQL Server?</span>

<p>The server parameter in the string attribute of the connection tag does not
refer to the DNS name of the server.  Rather it refers to an entry in the 
"interfaces" file.  The Sybase and FreeTDS libraries look for that file in 
default places, but if the file is installed somewhere else and the library 
can't find it, it will not be able to figure out what host/port the server
is running on.  One way to tell SQL Relay where the file is located is to
set the SYBASE environment variable to the directory containing the file before
starting SQL Relay.  Starting with version 0.32, the string attribute of the
connection tag takes a sybase parameter which sets the environment variable.</p>

<p>Another problem that people have connecting to Sybase/Microsoft SQL Server
is related to database selection.  Until version 0.32, the Sybase and FreeTDS
connection daemons ignored the "db" connectstring parameter.  This was an
oversight.  The connection daemons would connect to the correct server but
would not use the correct database.  Instead, the connection daemons would
be connected to the master database on that server.  This is fixed in 0.32.
To work around this problem in an older release it is necessary to fully
qualify table names if the table is not in the master database.  A fully
qualified table name is dbname.username.tablename.</p>

<a name="freetds"></a>
<span class="heading3">I know that Sybase and MS SQL Server support affected row counts, so why does the FreeTDS connection return -1's for affected rows?</span>

<p>Before version 0.53, calling the FreeTDS function to get the number of 
affected rows would cause a segmentation fault.  As of version 0.32, SQL Relay
figures out what version of FreeTDS is installed at compile time and only 
enables affected rows if the FreeTDS version is greater than 0.52.  If you 
compile against an earlier version of FreeTDS, a -1 is returned for affected 
rows as if the database didn't support the feature.</p>

<a name="money"></a>
<span class="heading3">Why are money types crashing my FreeTDS connections?</span>

<p>Before version 0.53, calling the FreeTDS function ct_fetch when a result
set had a MONEY or SMALLMONEY column in it would cause a segmentation fault.
As of version 0.32, SQL Relay figures out what version of FreeTDS is installed 
at compile time and only enables queries selecting MONEY or SMALLMONEY columns 
if the FreeTDS version is greater than 0.52.  If you compile against an earlier
version of FreeTDS, any attempt to run a query selecting MONEY or SMALLMONEY
column will fail with an error indicating that you should recompile SQL Relay
against a newer version of FreeTDS.</p>

<a name="sybasefreetdsdiff"></a>
<span class="heading3">What's the difference between the Sybase and FreeTDS 
connections?</span>

<p>The sqlr-connection-sybase program is compiled against Sybase ctlib; the 
libraries that come with Sybase Adaptive Server Enterprise.  They use a 
protocol called TDS (Tabular Data Stream) to talk to the database.</p>

<p>The sqlr-connection-freetds program is compiled against FreeTDS, an 
open-source implementation of the TDS protocol and ctlib.</p>

<p>Older versions of Microsoft SQL Server are compatible with Sybase ctlib, but
newer versions are not.  FreeTDS is compatible with all versions of Sybase 
Adaptive Server Enterprise and Microsoft SQL Server.</p>

<p>Unfortunately, FreeTDS is an incomplete implementation of TDS.  Several 
features are buggy, inconsistent or non-existent.  For example...</p>

<ul>
<li>Programs compiled against FreeTDS&lt;0.53 crash when fetching MONEY and 
SMALLMONEY datatypes.  SQL Relay handles this by preventing queries which
fetch MONEY or SMALLMONEY datatypes from running if it's compiled against 
FreeTDS&lt;0.53.</li>
<li>Programs compiled against FreeTDS&lt;0.53 crash when attempting to find out
how many rows were affected by an insert/update/delete query.  SQL Relay handles
this by not requesting the affected row count and just returning -1 if it's
compiled against FreeTDS&lt;0.53.</li>
<li>Bind variables are unsupported.  The ct_param and ct_setparam functions
simply don't exist in FreeTDS ctlib.  SQL Relay handles this by faking input 
bind variables in the same manner that the SQLite and
MDB Tools connections do.</li>
<li>FLOAT, NUMERIC and DECIMAL datatypes are all represented as FLOAT 
datatypes.  There doesn't appear to be any workaround for this.</li>
<li>Columns of type CHAR have trailing spaces truncated.  For a CHAR(20) column,
inserting a value 
<pre>'hello'</pre>
will be returned as
<pre>'hello'</pre> 
instead of
<pre>'hello               '</pre>
To add to the problem CHAR and VARCHAR datatypes are both represented as CHAR 
in FreeTDS and Sybase ctlib, so there's no good way to know whether to append 
trailing spaces or not.</li>
<li>Under Sybase ctlib, MONEY datatypes have 2 decmial places by default but 
can have up to 4.  Under FreeTDS, they have 4 decimal places.</li>
<li>Date formats are slightly different.  FreeTDS left-pads single digit days 
and hours with a single 0.  For example, Sybase ctlib returns 1:00 AM on 
01/01/2001 as
<pre>Jan  1 2001  1:00AM'</pre>
while FreeTDS returns it as
<pre>Jan 01 2001 01:00AM'</pre></li>
<li>Sybase ctlib returns REAL and FLOAT datatypes with arbitrary decimal 
places.  Inserting 1.1 as a REAL or FLOAT type then selecting it might return 
1.09888874310 or something similar.  With FreeTDS, inserting 1.1 then selecting
it returns 1.1.  Sybase behaves like FreeTDS for DECIMAL and NUMBER types.</li>
<li>FreeTDS only supports one cursor per connection.  You can write code that
uses multiple cursors, but it will eventually throw errors like: "Error:
Attempt to initiate a new SQL Server operation with results pending."  Prior
to SQL Relay 0.37, this was a common error to see under certain circumstances.
As of SQL Relay 0.37 the connection daemon funnels everything through a single
cursor and this error should no longer appear.  From the client-side as long as
you don't use setResultSetBufferSize() to specify a buffer size other than 0,
it will appear like more than 1 cursor is available, but in fact only 1 is
being used.</ul>

<p>There are possibly other inconsistencies, but these are the only ones that 
I've run into so far.</p>

<p>FreeTDS is great software despite its inconsistencies.  Sybase ctlib is 
impossibly complex.  Anyone attempting to re-engineer it is braver than I am.  
I'm impressed that FreeTDS works as well as it does, and it's getting better 
with each release.</p>

<a name="postgresql"></a>
<span class="heading3">Postgresql's native API uses numbers for types but
SQL Relay mangles them.  How do I get the numbers?</span>

<p>Prior to version 0.28, SQL Relay mangled Postgresql numeric types into
pseudo-standard datatype names.  I read a thread in a discussion group 
indicating that someone was specifically unhappy with SQL Relay because of this
behavior though, and decided to change it.  So, as of version 0.28, by default,
SQL Relay returns numeric types when run against Postgresql.  If you prefer
getting type names, you can set the mangletypes connect string value to "yes"
in your sqlrelay.conf file.</p>

<p>For example, in version 0.28 or higher the following connectstring will 
instruct SQL Relay to return type names instead of numbers:</p>

<blockquote>
user=myuser;password=mypass;db=testdb;mangletypes=yes
</blockquote>

<p>In version 0.28 or higher the following connectstring will instruct SQL 
Relay to return type numbers:</p>

<blockquote>
user=myuser;password=mypass;db=testdb;mangletypes=no
</blockquote>

<p>Leaving the mangletypes parameter out altogether is the same as setting it
to "no".</p>

<a name="nlslang"></a>
<span class="heading3">I'm using a non-ascii character set in my Oracle database, why am I get get garbage back when I do a select?</span>

<p>When the Oracle connection daemon fetches data from the database, the
Oracle client library converts the data from whatever character set the
database is using into whatever character set is specified by the NLS_LANG
environment variable.</p>

<p>If NLS_LANG isn't set, is set to a character set that doesn't contain some
of the characters that are stored in the database, or is set to a character set
that is in some other way incompatible with the character set of the database,
some of the data will be converted into garbage.</p>

<p><b>Note:</b> NLS_LANG must be set properly on the machine running the SQL
Relay <i><b>servers</b></i>, as that is the machine executing code from the
Oracle client library.  It's not uncommon to forget to set NLS_LANG on that
machine and then have things break suddenly when switching from using the
Oracle client library directly to using SQL Relay.</p>

<p><b>Note:</b> When SQL Relay starts at boot time, the SQL Relay init script
has no particular environment.  So, if NLS_LANG is set in /etc/profile or
/etc/bashrc or some other shell's init script, it may not be picked up by the
SQL Relay init script.  In version 0.37, an nls_lang parameter was added to the
Oracle connection string parameters to address this issue.  When running
versions older than 0.37, you'll need to edit the init script and add a line to
set the NLS_LANG environment variable.</p>

<a name="sqliterowid"></a>
<span class="heading3">How do I get the last insert rowid from SQLite?</span>

<p>You can run a special query: "SELECT LAST INSERT ROWID".  When SQL Relay
sees this query, it returns a result set with a single field containing the
last insert rowid.</p>

<a name="oracleosauth"></a>
<span class="heading3">How do I use Oracle's OS-Authentication (users identified externally)?</span>

<p>To set up Oracle to authenticate a user against the OS, first create a user
in linux/unix, then log into oracle as the sys user and create a corresponding
user as follows.  In this example, we'll assume that you created an OS-level
user named <b>mused</b>:</p>

<blockquote>
<pre>
<font color="#a52a2a"><b>CREATE</b></font> <font color="#6a5acd">USER</font> ops$mused <font color="#6a5acd">IDENTIFIED</font> EXTERNALLY
<font color="#a52a2a"><b>GRANT</b></font> <font color="#6a5acd">CONNECT</font> <font color="#6a5acd">TO</font> ops$mused
</pre>
</blockquote>

<p>Now you can log in as <b>mused</b> and connect to the database using
<b>sqlplus /</b> and you will not be prompted for a username and password.</p>

<p>In the sqlrelay.conf file, set the runasuser attribute of the instance tag
to the user that you want to connect as and leave out the user, password and
oracle_sid parameters of the string attribute of the connection tag.</p>

<p>For example, here's a normal (non-OS-authentication) configuration which
connects to the ora1 instance of oracle using testuser/testpassword:</p>

<blockquote>
<pre>
<font color="#008b8b">&lt;</font><font color="#008b8b">instance</font><font color="#008b8b"> ... </font><font color="#2e8b57"><b>runasuser</b></font>=<font color="#ff00ff">&quot;oracle&quot;</font><font color="#008b8b"> ...&gt;</font>
        ...
        <font color="#008b8b">&lt;</font><font color="#008b8b">connections</font><font color="#008b8b">&gt;</font>
                <font color="#008b8b">&lt;</font><font color="#008b8b">connection</font><font color="#008b8b"> ... </font><font color="#2e8b57"><b>string</b></font>=<font color="#ff00ff">&quot;user=testuser;password=testpassword;oracle_sid=ora1&quot;</font><font color="#008b8b"> .../&gt;</font>
        <font color="#008b8b">&lt;/connections&gt;</font>
<font color="#008b8b">&lt;/instance&gt;</font>
</pre>
</blockquote>

<p>Here's one that uses the oracle OS-user to connect.  This is analagous to
logging in as oracle and running <b>sqlplus /</b>:</p>

<blockquote>
<pre>
<font color="#008b8b">&lt;</font><font color="#008b8b">instance</font><font color="#008b8b"> ... </font><font color="#2e8b57"><b>runasuser</b></font>=<font color="#ff00ff">&quot;oracle&quot;</font><font color="#008b8b"> ...&gt;</font>
        ...
        <font color="#008b8b">&lt;</font><font color="#008b8b">connections</font><font color="#008b8b">&gt;</font>
                <font color="#008b8b">&lt;</font><font color="#008b8b">connection</font><font color="#008b8b"> ... </font><font color="#2e8b57"><b>string</b></font>=<font color="#ff00ff">&quot;&quot;</font><font color="#008b8b"> .../&gt;</font>
        <font color="#008b8b">&lt;/connections&gt;</font>
<font color="#008b8b">&lt;/instance&gt;</font>
</pre>
</blockquote>

<p>It's possible to set up an externally authenticated user to have access to
other SID's and use <b>sqlplus /@someotherschema</b> to connect to them.  To
configure SQL Relay to connect to another schema, just add the oracle_sid
parameter to the string attribute of the connection tag.  For example, use
the following to connect to ora1.<p>

<blockquote>
<pre>
        <font color="#008b8b">&lt;</font><font color="#008b8b">connection</font><font color="#008b8b"> ... </font><font color="#2e8b57"><b>string</b></font>=<font color="#ff00ff">&quot;oracle_sid=ora1&quot;</font><font color="#008b8b"> .../&gt;</font>
</pre>
</blockquote>

<a name="mysqltrans"></a>
<span class="heading3">Why aren't commit's working in MySQL?</span>

<p>Some versions of MySQL don't support transactions at all.  But modern
versions do, if you create tables using a table type that supports transactions.
The InnoDB table type is the most commonly used transaction-supporting table
type.  To use the InnoDB table type, use a create statement like the
following:</p>

<blockquote>
<pre>
<font color="#a52a2a"><b>create</b></font> <font color="#6a5acd">table</font> testtable (col1 int) <font color="#6a5acd">type</font>=innodb
</pre>
</blockquote>

<p>However, you may be surprised at MySQL's behavior even when you're using
InnoDB tables or another table type that supports transactions.</p>

<p>With most databases, if a client has a connection open and commits a set of
inserts, updates or deletes in, then those changes are immediately visible to
other clients using separate connections.</p>

<p>MySQL doesn't support this behavior, at least not with InnoDB tables.
Instead, with MySQL, after beginning a transaction, the database will appear to
be unaffected by queries run in other transactions until the transaction you
are running is committed.  So, if another client commits a set of inserts, you
won't see them until you commit your transaction.</p>

<p>If you're in autocommit mode though and haven't begun a transaction, then
you will immediately see all changes committed by other clients.  But as soon
as you begin a transaction, you will no longer see changes to the database
until you commit your transaction.</p>

<p>It's really a bit more complicated than that.  MySQL supports several
different <i>isolation levels</i>, each with slightly different behavior.
See the official MySQL documentation for more information on this.</p>

<p>It's possible (probable?) that non-MySQL databases have different isolation
levels as well, but I don't have any experience with different isolation levels
in other databases.  It's also possible that there is some way to get the
"normal" behavior out of MySQL, perhaps by using a different table type, but
again I don't have any experience with that.  If anyone has insight into this,
please <a href="mailto:david.muse@firstworks.com">email me</a> about it.</p>

<p>In SQL Relay 0.39, when using MySQL, whenver a client connects to the server,
the MySQL connection daemon executes a commit before running the first query to
sync things up.  This emulates the behavior that a client would expect if it
was making a new connection directly to the database.</p>

<a name="db2login"></a>
<span class="heading3">Why does the first DB2 query causes SQL Relay to reconnect to the DB?</span>

<p>This started in SQL Relay 0.39, see <a href="gettingstarted/db2.html#caveat">SQL Relay - Getting Started With IBM DB2</a> for information about this.</p>

<span class="heading3">Programming Questions</span><br><br>

<a name="php"></a>
<span class="heading3">My PHP application uses up all of the SQL Relay
connections, and I keep having to restart SQL Relay.  What gives?</span>

<p>Prior to version 0.36, PHP scripts needed to set up a shutdown handler to
clean up connections and cursors and call register_shutdown_function() or
call ignore_user_abort().  Otherwise, if someone hit stop in their browser, the
page would stop executing immediately and wouldn't close its connections to the
SQL Relay server.  In version 0.36, cleanup code was added to the PHP module,
making it unnecessary to set up a shutdown handler.</p>

<p>The cleanup code works with PHP 4.1 or greater.  If you're using PHP 4.0,
then the old issue still exists.  Below is a description of the issue and
how to deal with it.</p>

<p>PHP pages that use SQL Relay are usually set up like this:</p>

<blockquote>
<pre>
<font color="#6b59ce">&lt;?</font>
<font color="#008a8c">dl</font><font color="#6b59ce">(</font>&quot;<font color="#ff00ff">sql_relay.so</font>&quot;<font color="#6b59ce">)</font>;

<font color="#a52829"><b>var</b></font> <font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#a52829"><b>=</b></font>sqlrcon_alloc<font color="#6b59ce">(</font><font color="#a52829"><b>...</b></font><font color="#6b59ce">)</font>;
<font color="#a52829"><b>var</b></font> <font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font><font color="#a52829"><b>=</b></font>sqlrcur_alloc<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#6b59ce">)</font>;

<font color="#a52829"><b>...</b></font> <font color="#a52829"><b>do</b></font> some stuff <font color="#a52829"><b>...</b></font>

sqlrcur_free<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font><font color="#6b59ce">)</font>
sqlrcon_free<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#6b59ce">)</font>
<font color="#6b59ce">?&gt;</font>
</pre>
</blockquote>

<p>If someone clicks stop in their browser before sqlrcur_free is called,
execution of the page stops there and sqlrcur_free never gets called.  As a
result, the socket connection between the web server and SQL Relay is never
severed and the SQL Relay connection remains occupied even though the PHP page
that opened the connection isn't running anymore.</p>

<p>The best solution to this problem is to create a shutdown function and
register it with a call to register_shutdown_function().</p>

<p>For example:</p>

<blockquote>
<pre>
<font color="#6b59ce">&lt;?</font>
<font color="#008a8c">dl</font><font color="#6b59ce">(</font>&quot;<font color="#ff00ff">sql_relay.so</font>&quot;<font color="#6b59ce">)</font>;

<font color="#a52829"><b>var</b></font> <font color="#a52829"><b>$</b></font><font color="#008a8c">con</font>;
<font color="#a52829"><b>var</b></font> <font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font>;

<font color="#a520f7">function</font> shutdown<font color="#6b59ce">()</font> <font color="#6b59ce">{</font>
        sqlrcur_free<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font><font color="#6b59ce">)</font>;
        sqlrcon_free<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#6b59ce">)</font>;
<font color="#6b59ce">}</font>

<font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#a52829"><b>=</b></font>sqlrcon_alloc<font color="#6b59ce">(</font><font color="#a52829"><b>...</b></font><font color="#6b59ce">)</font>;
<font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font><font color="#a52829"><b>=</b></font>sqlrcur_alloc<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#6b59ce">)</font>;

<font color="#008a8c">register_shutdown_function</font><font color="#6b59ce">(</font>shutdown<font color="#6b59ce">)</font>;

<font color="#a52829"><b>...</b></font> <font color="#a52829"><b>do</b></font> some stuff <font color="#a52829"><b>...</b></font>
<font color="#6b59ce">?&gt;</font>
</pre>
</blockquote>

<p>Note that in this example, sqlrcur_free() and sqlrcon_free() aren't called
at the end of the page.  This is because the shutdown function gets called
whether the page was aborted or completed successfully.</p>

<p>If you want to create a function who's contents are executed only on abort,
you can use the connection_aborted() function.  In the following code, the
shutdown() function free's the cursor and connection if the script was aborted
but lets the script free them on its own if it completed successfully.  This
code is redundant but it illustrates the functionality of connection_aborted().
</p>

<blockquote>
<pre>
<font color="#6b59ce">&lt;?</font>
<font color="#008a8c">dl</font><font color="#6b59ce">(</font>&quot;<font color="#ff00ff">sql_relay.so</font>&quot;<font color="#6b59ce">)</font>;

<font color="#a52829"><b>var</b></font> <font color="#a52829"><b>$</b></font><font color="#008a8c">con</font>;
<font color="#a52829"><b>var</b></font> <font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font>;

<font color="#a520f7">function</font> shutdown<font color="#6b59ce">()</font> <font color="#6b59ce">{</font>
        <font color="#a52829"><b>if</b></font> <font color="#6b59ce">(</font><font color="#008a8c">connection_aborted</font><font color="#6b59ce">())</font> <font color="#6b59ce">{</font>
                sqlrcur_free<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font><font color="#6b59ce">)</font>;
                sqlrcon_free<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#6b59ce">)</font>;
        <font color="#6b59ce">}</font>
<font color="#6b59ce">}</font>

<font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#a52829"><b>=</b></font>sqlrcon_alloc<font color="#6b59ce">(</font><font color="#a52829"><b>...</b></font><font color="#6b59ce">)</font>;
<font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font><font color="#a52829"><b>=</b></font>sqlrcur_alloc<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#6b59ce">)</font>;

<font color="#008a8c">register_shutdown_function</font><font color="#6b59ce">(</font>shutdown<font color="#6b59ce">)</font>;

<font color="#a52829"><b>...</b></font> <font color="#a52829"><b>do</b></font> some stuff <font color="#a52829"><b>...</b></font>

sqlrcur_free<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font><font color="#6b59ce">)</font>;
sqlrcon_free<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#6b59ce">)</font>;
<font color="#6b59ce">?&gt;</font>
</pre>
</blockquote>

<p>Another option is to call ignore_user_abort(TRUE) early on in the script.  If
ignore_user_abort() is used, then if a user clicks stop, the PHP script is not
aborted.  Rather, the remainder of its output is just never sent to the browser.
This solution is less efficient than using register_shutdown_function() but also
works.  For example:<p>

<blockquote>
<pre>
<font color="#6b59ce">&lt;?</font>
<font color="#008a8c">dl</font><font color="#6b59ce">(</font>&quot;<font color="#ff00ff">sql_relay.so</font>&quot;<font color="#6b59ce">)</font>;

<font color="#a52829"><b>var</b></font> <font color="#a52829"><b>$</b></font><font color="#008a8c">con</font>;
<font color="#a52829"><b>var</b></font> <font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font>;

<font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#a52829"><b>=</b></font>sqlrcon_alloc<font color="#6b59ce">(</font><font color="#a52829"><b>...</b></font><font color="#6b59ce">)</font>;
<font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font><font color="#a52829"><b>=</b></font>sqlrcur_alloc<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#6b59ce">)</font>;

<font color="#008a8c">ignore_user_abort</font><font color="#6b59ce">(</font><font color="#ff00ff">TRUE</font><font color="#6b59ce">)</font>;

<font color="#a52829"><b>...</b></font> <font color="#a52829"><b>do</b></font> some stuff <font color="#a52829"><b>...</b></font>

sqlrcur_free<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font><font color="#6b59ce">)</font>;
sqlrcon_free<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#6b59ce">)</font>;
<font color="#6b59ce">?&gt;</font>
</pre>
</blockquote>

<p>SQL Relay version 0.37 features an idle client timeout.  If a client remains
connected to SQL Relay for a configurable amount of time but does nothing, then
SQL Relay will close the connection to that client.  This should make the
results less catastrophic when people forget to call
register_shutdown_function() or ignore_user_abort() but is still less
efficient.</p>

<a name="buffer"></a>
<span class="heading3">Why does SQL Relay buffer the entire result set?</span>

<p>SQL Relay is targeted for web-based applications.  For the most part,
queries with relatively small result sets are used to build web pages.  For
small result sets, it more efficient to buffer the entire result set than to
step through it, building the page.  It's usually faster because it reduces
network round-trips and allows one program to drop the connection to SQL 
Relay, freeing it up for more programs to use while the first program builds 
its page.</p>

<a name="nobuffer"></a>
<span class="heading3">How do keep SQL Relay from buffering the entire result 
set?</span>

<p>For large result sets it can be impractical to buffer the entire result set.
Use the setResultSetBufferSize() method in the C++, Perl, Python, Ruby and Java
API's or the sqlrcur_setResultSetBufferSize() function in the C or PHP API's to 
specify how many rows of the result set to buffer at once.</p>

<a name="bindvars"></a>
<span class="heading3">How do I use bind variables?</span>

<p>That depends on the database you're using.  Oracle supports named bind
variables while other databases only support binds by position.  Below are
pseudocode examples of both.</p>

<p>Oracle example:</p>
<pre>
sqlrconnection	*con=new sqlrconnection(...);
sqlrcursor	*cur=new sqlrcursor(cur);
cur-&gt;prepareQuery("select * from table where charcol=:charval and intcol=:intval and floatcol=:floatval");
cur-&gt;inputBind("charval","hello");
cur-&gt;inputBind("intval",10);
cur-&gt;inputBind("floatval",5.5,1,1);
cur-&gt;executeQuery();
delete cur;
delete con;
</pre>

<p>Sybase/MS SQL Server example:</p>
<pre>
sqlrconnection	*con=new sqlrconnection(...);
sqlrcursor	*cur=new sqlrcursor(cur);
cur-&gt;prepareQuery("select * from table where charcol=@charval and intcol=@intval and floatcol=@floatval");
cur-&gt;inputBind("charval","hello");
cur-&gt;inputBind("intval",10);
cur-&gt;inputBind("floatval",5.5,1,1);
cur-&gt;executeQuery();
delete cur;
delete con;
</pre>

<p>Other DB example:</p>
<pre>
sqlrconnection	*con=new sqlrconnection(...);
sqlrcursor	*cur=new sqlrcursor(cur);
cur-&gt;prepareQuery("select * from table where charcol=? and intcol=? and floatcol=?");
cur-&gt;inputBind("0","hello");
cur-&gt;inputBind("1",10);
cur-&gt;inputBind("2",5.5,1,1);
cur-&gt;executeQuery();
delete cur;
delete con;
</pre>

<p>Output bind variables work similarly but are only supported on Oracle.</p>

<pre>
sqlrconnection	*con=new sqlrconnection(...);
sqlrcursor	*cur=new sqlrcursor(con);
cur-&gt;prepareQuery("insert into table values ('hello') returning :charval");
cur-&gt;defineOutputBind("charval","hello",10);
cur-&gt;executeQuery();
cout &lt;&lt; "charval is: " &lt;&lt; cur-&gt;getOutputBind("charval") &lt;&lt; endl;
delete con;
delete cur;
</pre>

<a name="vectorbinds"></a>
<span class="heading3">Why can't I do vector binds?</span>

<p>Vector binds just aren't implemented yet.</p>

<a name="storedprocs"></a>
<span class="heading3">How do I run stored procedures?</span>

<p>Stored procedures work in most databases that support them, with some
caveats.  Stored procedures are not supported when using FreeTDS against
Sybase/MS SQL Server.  Binding clob/blob values are only supported in
Oracle 8i or greater.  When using stored procedures with Sybase, output
parameters must be varchar types.</p>

<p>The syntax for creating and executing a stored procedure is different for
each database.  Each of the "Programming with SQL Relay using the XXX API"
documents explain how to use stored procedures in detail.</p>

<a name="returning"></a>
<span class="heading3">How do I get data out of DML with RETURNING 
clauses?</span>

<p>DML with RETURNING clauses are only known to work with Oracle.  You can use 
output bind variables to get data out of DML with RETURNING clauses if the 
values returned are scalar.  Vector values are not supported.  For example:</p>

<pre>
insert into testtable values ("one",2) returning :first, :second
</pre>

<a name="temptables"></a>
<span class="heading3">Do temporary tables work with SQL Relay?</span>

<p>Yes.  Support for PostgreSQL temporary tables was added in version 0.34 and
support for all other databases was added in 0.35.</p>

<p>The reason temporary tables are an issue is because normally temporary tables
are either dropped or truncated when an application closes its connection to
the database or commits/rolls-back a transaction.   Transaction-scope tables
work naturally with SQL Relay.  But, since SQL Relay never logs out of the
database, the database doesn't know to drop or truncate tables when an
application closes its connection to SQL Relay.  To remedy this, SQL Relay
parses each query according to the rules for creating a temporary table for
the database that it is connected to and keeps a list of temporary tables.
Then, when the application closes its connection, SQL Relay drops or
truncates the tables as appropriate.</p>

<p>In Oracle, temporary tables are never dropped when the session or
transaction ends, only truncated.  Transaction-scope tables are handled
naturally by the database, but session-scope tables must be handled by
SQL Relay.  In other databases, a create table query would have to have been
issued but not with Oracle.  SQL Relay only knows to truncate the table if a
create table query was issued.  So, when using session-scope tables in Oracle,
you should issue a create table query, even if you know the table already
exists.  The query will fail, but SQL Relay will know to truncate the table
when the session ends.</p>

<a name="threadsafe"></a>
<span class="heading3">Is the SQL Relay API thread-safe?</span>

<p>Yes.  However, you cannot share an instance of an sqlrconnection or
sqlrcursor between threads without protecting the calls with mutexes.</p>

<a name="clientcache"></a>
<span class="heading3">How does client-side result-set caching work?</span>

<p>When the cacheToFile() and setCacheTtl() methods are called prior to running
a query, the client caches the result set from the query in a file on the
local file system and attaches a time-to-live tag to the file.  The full 
pathname of this file can be retrieved using the getCacheFileName() method.</p>

<p>The file sits in the cache directory (usually 
/usr/local/firstworks/var/sqlrelay/cache) until it is removed by the 
sqlr-cachemanager program.  sqlr-cachemanager scans the files in the cache
directory every so often and removes the ones who's time to live has expired.
</p>

<p>Until the file is removed, other applications can open the file by name
using the openCachedResultSet() methods.  At this point, the API acts as if it
had run a query that generated that result set.</p>

<a name="servercache"></a>
<span class="heading3">What about server-side result-set caching?</span>

<p>Server-side result set caching has not been implemented.  This feature is 
on the TODO list though.</p>

<a name="defineoutputbind"></a>
<span class="heading3">What happened to defineOutputBind?</span>

<p>defineOutputBind defined a string output bind.  Version 0.37 added support
for integer and double output binds and defineOutputBind was replaced with
defineOutputBindString, defineOutputBindInteger and defineOutputBindDouble and
getOutputBind was replaced with getOutputBindString, getOutputBindInteger and
getOutputBindDouble.</p>

<p>Prior to 0.37, stored procedures had to convert integers and doubles to
strings before returning them.  As of 0.37, this is no longer necessary.</p>

<p>Apps which used defineOutputBind/getOutputBind must replace those calls with
defineOutputBindString/getOutputBindString to get the exact same behavior.</p>

<a name="mysqlinsertid"></a>
<span class="heading3">MySQL Provides mysql_insert_id().  How do I get that from SQL Relay?</span><br><br>

<p>SQL Relay doesn't provide an API call like mysql_insert_id(), however, when
using MySQL as a backend database, you can run the query
"select last_insert_id()" to get the last insert_id.</p>

<a name="tweaks"></a>
<span class="heading3">What system parameters can I tweak to get better performance out of SQL Relay?</span><br><br>

<p>See <a href="tuning.html">Tuning SQL Relay...</a></p>

<a name="firewallhang"></a>
<span class="heading3">If I leave SQL Relay idle for a long time, then come back and try to use it, my program gets hung or I get a low level TCP error from the database.  What does that mean?</span><br><br>

<p>If you are running SQL Relay on one machine and the database on a seperate
machine, and there is a firewall between the two machines, and no queries have
been run in a while, the firewall may "close" the connection between SQL Relay
and the database by discarding any packets that it receives from either.
The firewall does not send "close packets" to either SQL Relay or the database,
so SQL Relay thinks it's still connected to the database, but when it sends a
query, the firewall discards it.</p>

<p>Some database client API's catch this condition and return an obscure
error but many do not, they just hang silently forever.</p>

<p>Cisco PIX firewalls are known to cause this problem.  Others may as well.</p>

<p>Allegedly, an IOS upgrade will solve the problem for Cisco PIX firewalls,
but I don't know what version you have to upgrade to.</p>

<p>Another solution is to turn off the time out.</p>

<p>Yet another solution is to set up a cron job that runs a query periodically
and keeps the connection from timing out.</p>

<p>You could also just set up the timeout to be so long that it's really
unlikely that it will ever occur.  The PIX firewall's default is 1 hour.  You
can change it from the PIX's command line using a command such as the
following:</p>

<blockquote><b>
timeout conn 04:00:00
</b></blockquote>

<p>You can also set it using the graphical web interface as follows:</p>

<p><img src="WPM_4877_1.PNG"></p>

<a name="ttl"></a>
<span class="heading3">Why doesn't the last connection die off when I set connections=0 and maxconnections to some larger number and let SQL Relay go idle for a long time?</span><br><br>

<p>When SQL Relay first starts up, since no connections are started, no connections are registered with the listener.  Clients connect, connections fire up to handle them, clients disconnect, the connections time out and die.  However, one of the connections will still remain registered with the listener as an "available connection".  This connection cannot die off.</p>

<p>It's really, really complicated why.  I tried lots of approaches and couldn't find one that didn't have a fundamental problem.</p>

<p>Basically, here's the code:</p>

<blockquote>
<pre>
listener:
	acquireExclusiveAccessToSharedMemoryAmongListenerProcesses();
	waitForConnectionToSignalUsToRead();
	readRegistration();
	signalConnectionThatWeHaveRead();
	releaseEclusiveAccessToSharedMemoryAmongListenerProcesses();

connection:
	acquireExclusiveAccessToSharedMemoryAmongConnectionProcesses();
	writeRegistration();
	signalListenerToRead();
	waitForListenerToSignalUsThatItHasRead();
	releaseEclusiveAccessToSharedMemoryAmongConnectionProcesses();
</pre>
</blockquote>

<p>If the connection dies between signalListenerToRead() and
waitForListenerToSignalUsThatItHasRead() or while waiting then obscure problems
occur.  Most notably, the next connection with a that starts up will not die
off.  Also, it is certain that the currently registered connection will die
at this point because that's where it sits and waits for the listener to hand
it a client.</p>

<p>Fundamentally, the listener needs to know that the connection has died off
and needs not to signalConnectionThatWeHaveRead().  For the time being, I'm not
certain how to do that.  So for now, I avoid the problem by making it impossible
for a connection to die off there.</p>

<p>Of course, it's always possible to kill the connection manually.  In that
case, you would cause the problem that I'm trying to avoid.</p>

<p>

<a name="nobody"></a>
<span class="heading3">Whenever I set maxconnections&gt;connections, it doesn't ever start any new connections and I get "Couldn't parse config file ..." errors on the screen.  What's up with that?</span><br><br>

<p>SQL Relay is typically started as root.  The sqlr-start command runs as root
and starts up the sqlr-listener, sqlr-connection and sqlr-scaler processes as
root.  They then read the config file and switch to run as whatever user/group
are specified in the runasuser and runasgroup parameters.  The sqlr-scaler is
most likely now running as a non-root user (such as nobody).  When it spawns
new connections, they start out running as that same non-root user.  The root
user can always read the config file, but the config file may not have
permissions set so that the non-root user can read it.  In that case, the
sqlr-connection daemon will start up, fail to read the config file, display a
message and exit.</p>

<p>To remedy the situation, make sure that the sqlrelay.conf file is readable
by each of the users and groups specified in the runasuser/runasgroup
attributes inside the file.</p>

<a name="gui"></a>
<span class="heading3">Where'd the configuration GUI go?</span><br><br>

<p>The GTK-based configuration GUI was removed in version 0.40.  It was buggy,
used legacy GTK+ version 1.2 and virtually nobody used it.</p>

<a name="socketonly"></a>
<span class="heading3">How can I get SQL Relay to only listen on the unix socket and not the inet port?</span><br><br>

<p>This was broken prior to version 0.40.  But in 0.40 or newer, you can just
set port="" in the instance tag of the sqlrelay.conf file or leave the attribute
out altogether.  However, in either case, you must supply a value for the socket
attribute.  If neither are specified then SQL Relay will fall back to listening
on the default port 9000 and not listening on the unix socket at all.</p>

<a name="timequeries"></a>
<span class="heading3">How can I get SQL Relay to log queries?</span><br><br>

<p>Version 0.40 introduced the <b>timequeriessec</b> and <b>timequeriesusec</b>
parameters, though they were not documented until version 0.41.  They can be
used to log queries which take longer than <b>timequeriessec</b> seconds and
<b>timequeriesusec</b> milliseconds to complete.  See
<a href="configuring.html">Configuring SQL Relay</a> for more information.</p>

<a name="dynamicscaling"></a>
<span class="heading3">If I set up SQL Relay to use dynamic scaling, it eventually spawns off as many connections as it can and they never die off.  What's up with that?</span><br><br>

<p>This can happen if a spike of client traffic comes in.  If configured to do so, SQL Relay will fork off more connection daemons to handle the traffic.  Later when the traffic dies down, the connections that were forked off will still end up handling clients.  Each connection daemon will experience longer idle periods than before the spike, but unless those idle periods are shorter than the <i>ttl</i> parameter, the connections will not die off.  Setting the <i>ttl</i> shorter should take care of this, but it may take a lot of fine tuning to find the correct <i>ttl</i>.</p>

<p>As of version 0.41, SQL Relay supports a new parameter: <i>maxsessioncount</i>.  If it is set greater than 0, then connections that were forked off will only handle that many client sessions before dying off, even if it doesn't go idle.  Using this new parameter will ensure that all forked-off connections will eventually die off.  Like the <i>ttl</i>, this parameter still requires some
tuning but setting it too low will result in poorer performance rather than
additional resource consumption.</p>

</body>
</html>
